package com.neusoft.hifly.commons.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.apache.commons.lang3.StringUtils;

import com.neusoft.hifly.commons.exception.SystemErrorException;
import com.neusoft.hifly.commons.web.WebUtil;
import com.neusoft.hifly.core.log.Log;

/**
 * Title: 核心类库
 * <p>
 * Description: 数据库工具类
 * <p>
 * Copyright: 2019
 * <p>
 * Company: 东软汉枫
 *
 * @author lh_neu
 * @version 1.0.2
 */
public final class DBUtil {

	/**
	 * 数据库类型集合
	 */
	public static final String[] DB_TYPE = new String[] { SQLUtil.DB_ORACLE, SQLUtil.DB_MYSQL, SQLUtil.DB_SQLSERVER,
			SQLUtil.DB_EDB, SQLUtil.DB_DB2, SQLUtil.DB_SYBASE, SQLUtil.DB_GBASE };

	/**
	 * 数据库驱动 edb
	 */
	public static final String DRIVER_EDB = "com.edb.Driver";

	/**
	 * 数据库驱动 oracle
	 */
	public static final String DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";

	/**
	 * 数据库驱动 mysql
	 */
	public static final String DRIVER_MYSQL = "com.mysql.jdbc.Driver";

	/**
	 * 数据库驱动 sqlserver
	 */
	public static final String DRIVER_SQLSERVER = "net.sourceforge.jtds.jdbc.Driver";

	/**
	 * 数据库驱动 sybase
	 */
	public static final String DRIVER_SYBASE = "com.sybase.jdbc2.jdbc.SybDriver";

	/**
	 * 数据库驱动 db2
	 */
	public static final String DRIVER_DB2 = "com.ibm.db2.jdbc.app.DB2Driver";

	/**
	 * 数据库驱动 gbase
	 */
	public static final String DRIVER_GBASE = "com.gbase.jdbc.Driver";

	/**
	 * 数据库URL edb
	 */
	public static final String URL_EDB = "jdbc:edb://<host_name>[<:5444>]/<dbname>";

	/**
	 * 数据库URL oracle
	 */
	public static final String URL_ORACLE = "jdbc:oracle:thin:@<server>[:<1521>]:<database_name>";

	/**
	 * 数据库URL mysql
	 * <p>
	 * 解决MySQL 5.x 版本BUG：jdbc为数值、日期行保存null时异常问题：zeroDateTimeBehavior=convertToNull
	 */
	public static final String URL_MYSQL = "jdbc:mysql://<hostname>[<:3306>]/<dbname>?useUnicode=true&characterEncoding=[<UTF-8>]&autoReconnect=true&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull";

	/**
	 * 数据库URL sqlserver jdbc:microsoft:sqlserver://<server_name>:<1433>
	 */
	public static final String URL_SQLSERVER = "jdbc:jtds:sqlserver://<server_name>[<:1433>];DatabaseName=<dbname>";

	/**
	 * 数据库URL sybase
	 */
	public static final String URL_SYBASE = "jdbc:sybase:Tds:<host_name>[<:5000>]/<dbname>";

	/**
	 * 数据库URL db2
	 */
	public static final String URL_DB2 = "jdbc:db2://<host_name>:<port>/<dbname>";

	/**
	 * 数据库URL gbase
	 */
	public static final String URL_GBASE = "jdbc:gbase://<host_name>[<:5258>]/<dbname>";

	// ###################### 数据库字段类型  ############################
	/**
	 * ORACLE数据类型
	 */
	public static final String[] ORACLE_TYPE = new String[] { "NVARCHAR2", "NUMBER", "DATE", "VARCHAR2", "CHAR", "LONG",
			"CLOB", "BLOB", "NCHAR" };
	/**
	 * MYSQL数据类型
	 */
	public static final String[] MYSQL_TYPE = new String[] { "VARCHAR", "INT", "DATETIME", "TEXT", "CHAR", "FLOAT",
			"DOUBLE", "DATE", "TIME", "BLOB", "LONGTEXT" };
	/**
	 * SQLSERVER数据类型
	 */
	public static final String[] SQLSERVER_TYPE = new String[] { "VARCHAR", "INT", "DATETIME", "TEXT", "NUMERIC",
			"CHAR", "NCHAR", "NVARCHAR", "FLOAT", "TINYINT" };

	private static DBUtil instance;

	/**
	 * 默认构造函数
	 */
	private DBUtil() {
	}

	/**
	 * 获取单例对象
	 *
	 * @return 对象
	 */
	public synchronized static DBUtil getInstanse() {
		if (DBUtil.instance == null) {
			DBUtil.instance = new DBUtil();
		}
		return DBUtil.instance;
	}

	/**
	 * 测试连接
	 *
	 * @param dbType
	 *            数据量类型
	 * @param url
	 *            数据库访问地址
	 * @param user
	 *            数据库用户名
	 * @param password
	 *            数据库密码
	 * @param conn
	 *            指定数据连接，若为空，则重新建立
	 * @return 是否测试成功
	 * @throws SystemErrorException
	 *             自定义异常
	 */
	public boolean testConn(final String dbType, final String url, final String user, final String password,
			Connection conn) throws SystemErrorException {
		final String driver = connectionDriver(dbType);
		final String sql = validationQuery(dbType);
		//		Log.error("测试数据连接: url = " + url + " user = " + user + " password = " + password + " driver = " + driver
		//				+ " sql = " + sql);
		if (StringUtils.isNotEmpty(sql)) {
			Statement stmt1 = null;
			try {
				Class.forName(driver).newInstance();
				if (conn == null && StringUtils.isNotEmpty(url)) {
					DriverManager.setLoginTimeout(5);
					conn = DriverManager.getConnection(url, user, password);
				}
				if (conn == null) {
					Log.error("测试连接创建失败 ! url = " + url + " user = " + user + " password = " + password + " driver = "
							+ driver + " sql = " + sql);
					return false;
				}
				stmt1 = conn.createStatement();
				stmt1.setQueryTimeout(3);
				stmt1.executeQuery(sql);
				return true;
			} catch (final Exception e) {
				Log.error(e.getMessage());
				throw new SystemErrorException("测试连接失败!" + e.getMessage() + "。url = " + url + " user = " + user
						+ " password = " + password + " driver = " + driver + " sql = " + sql);
			} finally {
				try {
					if (stmt1 != null) {
						stmt1.close();
						stmt1 = null;
					}
					if (conn != null) {
						if (!conn.isClosed()) {
							conn.close();
						}
						conn = null;
					}
				} catch (final Exception e) {
					Log.error(e.getMessage());
				}
			}
		}
		return false;
	}

	/**
	 * 初始化连接
	 *
	 * @author lh_neu
	 * @date 2013-12-21 上午9:34:36
	 * @param type
	 *            数据库类型
	 * @param ip
	 *            数据库IP/双机地址
	 * @param port
	 *            端口号
	 * @param sid
	 *            实例名
	 * @param characterEncoding
	 *            字符集
	 * @param username
	 *            登录名
	 * @param password
	 *            登录密码
	 * @return 连接
	 */
	public synchronized Connection connection(final String type, final String ip, final String port, final String sid,
			final String characterEncoding, final String username, final String password) {
		if (StringUtils.isNotEmpty(type) && StringUtils.isNotEmpty(ip) && StringUtils.isNotEmpty(port)
				&& StringUtils.isNotEmpty(sid) && StringUtils.isNotEmpty(username)
				&& StringUtils.isNotEmpty(password)) {
			try {
				final String driver = connectionDriver(type);
				Class.forName(driver).newInstance();
				return DriverManager.getConnection(connectionUrl(type, ip, port, sid, characterEncoding), username,
						password);

			} catch (final Exception e) {
				Log.error(e.getMessage());
			}
		}
		//		else {
		//			Log.error("参数不全,无法创建连接");
		//		}
		return null;
	}

	/**
	 * 根据数据库类型获取测试连接
	 *
	 * @param dbType
	 *            数据库类型
	 * @return 测试连接
	 */
	public String validationQuery(final String dbType) {
		String sql = "";
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1 FROM DUAL";
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1 FROM DUAL";
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1";
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1";
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1";
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1";
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(dbType)) {
			sql = "SELECT 1";
		}
		return sql;
	}

	/**
	 * 根据数据库类型获取驱动
	 *
	 * @param dbType
	 *            数据库类型
	 * @return 驱动
	 */
	public String connectionDriver(final String dbType) {
		String driver = "";
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_ORACLE;
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_EDB;
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_SQLSERVER;
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_MYSQL;
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_SYBASE;
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_DB2;
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(dbType)) {
			driver = DBUtil.DRIVER_GBASE;
		}
		return driver;
	}

	/**
	 * 获取数据库连接url
	 *
	 * @param type
	 *            数据库类型
	 * @param ip
	 *            数据库IP/双机地址
	 * @param port
	 *            端口号
	 * @param sid
	 *            实例名
	 * @param characterEncoding
	 *            字符集
	 * @return URL地址
	 */
	public String connectionUrl(final String type, final String ip, final String port, final String sid,
			final String characterEncoding) {
		if (StringUtils.isEmpty(type) || StringUtils.isEmpty(port) || StringUtils.isEmpty(sid)) {
			// 默认IP里面已经是拼接号的数据库URL地址了
			return ip;
		}
		String url = null;
		if (SQLUtil.DB_ORACLE.equalsIgnoreCase(type)) {
			if (WebUtil.isIPAdress(ip)) {
				url = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + sid;
			} else {
				// 匹配oracle双机
				url = "jdbc:oracle:thin:@" + ip;
			}
		} else if (SQLUtil.DB_EDB.equalsIgnoreCase(type)) {
			if (WebUtil.isIPAdress(ip)) {
				url = "jdbc:edb://" + ip + ":" + port + "/" + sid;
			}
			// edb的双机连接不知道怎么做，暂时先不处理
		} else if (SQLUtil.DB_MYSQL.equalsIgnoreCase(type)) {
			url = "jdbc:mysql://" + ip + ":" + port + "/" + sid;
			if (StringUtils.isNotEmpty(characterEncoding)) {
				url += "?useUnicode=true&characterEncoding=" + characterEncoding.toUpperCase()
						+ "&autoReconnect=true&zeroDateTimeBehavior=convertToNull";
			} else {
				url += "?autoReconnect=true&zeroDateTimeBehavior=convertToNull";
			}
			// mysql的双机连接不知道怎么做，暂时先不处理
		} else if (SQLUtil.DB_SQLSERVER.equalsIgnoreCase(type)) {
			url = "jdbc:microsoft:sqlserver://" + ip + ":" + port + ";DatabaseName=" + sid;
			// sqlserver的双机连接不知道怎么做，暂时先不处理
		} else if (SQLUtil.DB_SYBASE.equalsIgnoreCase(type)) {
			url = "jdbc:sybase:Tds:" + ip + ":" + port + "/" + sid;
			// sybase的双机连接不知道怎么做，暂时先不处理
		} else if (SQLUtil.DB_DB2.equalsIgnoreCase(type)) {
			url = "jdbc:db2://" + ip + ":" + port + "/" + sid;
			// db2的双机连接不知道怎么做，暂时先不处理
		} else if (SQLUtil.DB_GBASE.equalsIgnoreCase(type)) {
			url = "jdbc:gbase://" + ip + ":" + port + "/" + sid;
			// gbase的双机连接不知道怎么做，暂时先不处理
		}
		Log.debug("--------------------- 数据库连接地址：" + url);
		return url;
	}

	public static void main(final String[] args) {
		final String dbType = null;
		final String dbUrl = null;
		final String dbUser = null;
		final String dbPwd = null;
		try {
			DBUtil.getInstanse().testConn(dbType, dbUrl, dbUser, dbPwd, null);
		} catch (final SystemErrorException e) {
			e.printStackTrace();
		}
	}
}
